/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package Model;
import bean.*;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
/**
 *
 * @author cpn02
 */
public class ProModel {
    public static Product getProByID(long IDpro)
    {
        // lay ra san pham co ID duoc xac dinh bang IDpro
        Product pro=null;
        Connector cnntor = new Connector();
        String query = "select * from product where status='1' and IDpro="+IDpro;
          try {
                Statement st = cnntor.connect().createStatement();
                ResultSet rs = st.executeQuery(query);
                if(rs.next())
                {
                   pro = setPro(rs.getLong("IDpro"),rs.getString("desc"),rs.getString("gender"), rs.getInt("price"),rs.getInt("size"), rs.getInt("amount"), rs.getString("image"), rs.getFloat("promotion"), rs.getString("brand"), rs.getShort("cat"));
                }
          } catch (Exception ex)
          {
              ex.printStackTrace();
          }
        return pro;
    }
    public static ArrayList<Product> getPros(int startIndex,int amount)
    {
        //lay ra san pham co vi tri bat dau startIndex,
        ArrayList<Product> pros = new ArrayList<Product>();
        Connector cnntor = new Connector();
        String query = "select * from product where status='1' limit "+(startIndex-1)+","+amount;
          try {
                Statement st = cnntor.connect().createStatement();
                ResultSet rs = st.executeQuery(query);
                while(rs.next())
                {
                    Product pro = setPro(rs.getLong("IDpro"),rs.getString("desc"),rs.getString("gender"), rs.getInt("price"),rs.getInt("size"), rs.getShort("amount"), rs.getString("image"), rs.getFloat("promotion"), rs.getString("brand"), rs.getShort("cat"));
                    pros.add(pro);
                }
          } catch (Exception ex)
          {
              ex.printStackTrace();
          }
        return pros;
    }

    // nha mang san pham co cac dac tinh nhu san pham dieu kien ConPro , voi chi so bat dau va so luong xac dinh
    public static ArrayList<Product> getProsBySearchCon( int minSize, int maxSize,int gender, int cat , int startIndex, int amount)
    {
        // lay cac san pham thoa man dieu kien
        ArrayList<Product> pros = new ArrayList<Product>();
        Connector cnn = new Connector();
        try
        {
            Statement st = cnn.connect().createStatement();
            String query = "select * from product where 1 ";
            if(minSize > -1 && maxSize>=minSize)
            {
                query = query + "and size>="+minSize+" and size<="+maxSize+" ";
            }
            if(gender > -1)
            {
                query = query + "and gender='"+gender+"' ";
            }
            if(cat>-1)
            {
                query = query +"and cat="+cat+" ";
            }
            if(startIndex>0 && amount>-1)
            {
                query = query +"limit "+(startIndex-1)+","+amount;
            }
            ResultSet rs = st.executeQuery(query);
            while(rs.next())
            {
                Product pro = setPro(rs.getLong("IDpro"),rs.getString("desc"),rs.getString("gender"), rs.getInt("price"),rs.getInt("size"), rs.getShort("amount"), rs.getString("image"), rs.getFloat("promotion"), rs.getString("brand"), rs.getShort("cat"));
                pros.add(pro);
            }
        }
        catch(ClassNotFoundException cex)
        {
            cex.printStackTrace();
        }
        catch(SQLException sex)
        {
            sex.printStackTrace();
        }
        return pros;
    }
    public static int getProsNumBySearchCon(int minSize, int maxSize,int gender, int cat)
    {
        // dem so san pham thoa man yeu cau
        ArrayList<Product> pros = new ArrayList<Product>();
        int num = 0 ;
        Connector cnn = new Connector();
        try
        {
            Statement st = cnn.connect().createStatement();
            String query = "select count(*) num from product where 1 ";
            if(minSize > -1 && maxSize>=minSize)
            {
                query = query + "and size>="+minSize+" and size<="+maxSize+" ";
            }
            if(gender > -1)
            {
                query = query + "and gender='"+gender+"' ";
            }
            if(cat>-1)
            {
                query = query +"and cat="+cat+" ";
            }
            ResultSet rs = st.executeQuery(query);
            if(rs.next())
            {
                num = rs.getInt("num");
            }

        }
        catch(ClassNotFoundException cex)
        {
            cex.printStackTrace();
        }
        catch(SQLException sex)
        {
            sex.printStackTrace();
        }
        return num;

    }
    private static Product setPro(long IDpro,String desc,String gender,int price,int size,int amount,String image, float promotion,String brand,short category)
    {
        Product pro = new Product();
        pro.setIDpro(IDpro);
        pro.setDesc(desc);
        pro.setGender(gender);
        pro.setPrice(price);
        pro.setSize(size);
        pro.setAmount(amount);
        pro.setImage(image);
        pro.setBrand(brand);
        pro.setPromotion(promotion);
        pro.setCategory(category);
        return pro;
    }

    public static ArrayList<Product> getProByOrder()
    {
         ArrayList<Product> pros = new ArrayList<Product>();
        int num = 0 ;
        Connector cnn = new Connector();
        try
        {
            Statement st = cnn.connect().createStatement();
            String query = "select * from product where IDpro in (select IDpro from `orderdetail` where 1  group by IDpro having count(IDpro) = (select count(IDpro) from orderdetail group by IDpro asc limit 0,1))";
            ResultSet rs = st.executeQuery(query);
            while(rs.next())
            {
                Product pro = setPro(rs.getLong("IDpro"),rs.getString("desc"),rs.getString("gender"), rs.getInt("price"),rs.getInt("size"), rs.getShort("amount"), rs.getString("image"), rs.getFloat("promotion"), rs.getString("brand"), rs.getShort("cat"));
                pros.add(pro);
            }

        }
        catch(ClassNotFoundException cex)
        {
            cex.printStackTrace();
        }
        catch(SQLException sex)
        {
            sex.printStackTrace();
        }
        return pros;
    }
     public static int getMaxSize()
     {
         Connector cnn = new Connector();
         int maxsize = -1;
         try{
             Statement st = cnn.connect().createStatement();
             String query = "select max(size) size from product ";
             ResultSet rs = st.executeQuery(query);
             if(rs.next())
                 maxsize = rs.getInt("size");
         }
         catch (ClassNotFoundException ex)
         {
             ex.printStackTrace();
         }
         catch(SQLException sex)
         {
             sex.printStackTrace();
         }
         return maxsize;
     }

     public static void buy(Customer cus, ArrayList<CartBean> carts)
    {
// thuc hien xay dung hoa don ,cap nhap produc khi khach muh
         Connector cnn = new Connector();
         Date d = new Date();
        String stdate = d.getYear()+"-"+d.getMonth()+"-"+d.getDate();
        try {
                Statement st = cnn.connect().createStatement();
                String query = "insert into `order` values(NULL,"+cus.getIDCus()+",'"+stdate+"','0')";
                st.execute(query);
                query = "select IDO from `order` ORDER BY IDO Desc limit 0,1 ";
                ResultSet rs = st.executeQuery(query);
                rs.next();
                int IDO = rs.getInt("IDO");
                Iterator<CartBean> it = carts.iterator();
                CartBean cart ;
                Product pro;
                while(it.hasNext())
                {
                    cart = it.next();
                    pro = cart.getPro();
                    long price = (long)(pro.getPrice()*(1-pro.getPromotion()/100));
                    query = "insert into orderdetail values("+IDO+","+pro.getIDpro()+","+cart.getAmount()+","+price+")";
                    st.execute(query);
                    int newAmount = pro.getAmount()-cart.getAmount();
                    query = "update product set amount="+newAmount+" where IDpro="+pro.getIDpro();
                    st.execute(query);
                }
        }catch (ClassNotFoundException ex) {
            ex.printStackTrace();
        } catch (SQLException ex)
        {
            ex.printStackTrace();
        }
        finally
        {
            cnn.closeDB();
        }
    }

}
